package ro.gateway.aida.obj.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.Activity;
import ro.gateway.aida.obj.Category;
import ro.gateway.aida.obj.OrganizationInv;
import ro.gateway.aida.obj.OrganizationRole;


/**
 * <p>Title: Romanian AIDA</p> <p>Description: :D application</p> <p>Copyright: Copyright (comparator) 2003</p>
 * <p>Company: Romania Development Gateway </p>
 *
 * @author Mihai Popoaei, mihai_popoaei@yahoo.com, smike@intellisource.ro
 * @version 1.0-* @version $Id: DBStuff.java,v 1.1 2004/10/24 23:37:11 mihaipostelnicu Exp $
 */
public class DBStuff extends DBPersistenceManager {
     private DBStuff( PersistenceToken token ) {
          super( token );
     }

     public static DBStuff getManager( PersistenceToken token ) {
          return new DBStuff( token );
     }

     public final void insertDonorIds( Activity activity )
        throws SQLException {

          OrganizationInv[] donori = activity.
             getOrgsByLRId( OrganizationRole.DONOR_ORG_ROLE );
          if ( donori == null ) {
               return;
          }

          Connection con = getConnection();

          try {
               PreparedStatement ps = con.prepareStatement( INSERT_DONOR_ID );
               for ( int i = 0; i < donori.length; i++ ) {
                    ps.clearParameters();
                    String did = activity.getDonorIdForOrg( donori[ i ].getOrg_id() );
                    if ( did == null ) continue;

                    ps.setLong( 1, activity.getId() );
                    ps.setLong( 2, donori[ i ].getOrg_id() );
                    ps.setString( 3, did );
                    ps.executeUpdate();
               }
               ps.close();
          } catch ( SQLException sqlEx ) {
               throw sqlEx;
          } finally {
               con.close();
          }

     }

     public void deleteDonorIdsForActivity( long activity_id )
        throws SQLException {
          Connection con = getConnection();

          try {
               PreparedStatement ps = con.prepareStatement( DELETE_DONOR_IDS_FOR_ACT );
               ps.setLong( 1, activity_id );
               ps.executeUpdate();
               ps.close();
          } catch ( SQLException sqlEx ) {
               throw sqlEx;
          } finally {
               con.close();
          }
     }

     public void setDonorIds( Activity activity )
        throws SQLException {

          Connection con = getConnection();

          try {
               PreparedStatement ps = con.prepareStatement( GET_DONOR_IDS_FOR_ACT );
               ps.setLong( 1, activity.getId() );
               ResultSet rs = ps.executeQuery();
               while ( rs.next() ) {
                    activity.setDonorIdForOrg( rs.getLong( 1 ), rs.getString( 2 ) );
               }
               rs.close();
               ps.close();
          } catch ( SQLException sqlEx ) {
               throw sqlEx;
          } finally {
               con.close();
          }

          return;
     }

     public void delete_cats_for_activity( long activity_id )
        throws SQLException {
          Connection con = getConnection();

          try {
               PreparedStatement ps = con.prepareStatement( DELETE_CATS_FOR_ACTIVITY );
               ps.setLong( 1, activity_id );
               ps.executeUpdate();
               ps.close();
          } catch ( SQLException sqlEx ) {
               throw sqlEx;
          } finally {
               con.close();
          }
     }

     public void insert_act_cats_links( Activity bean )
        throws SQLException {
          Category[] cats = bean.getCategoriesArray();
          if ( cats == null || cats.length < 1 ) return;

          Connection con = getConnection();

          try {
               PreparedStatement ps = con.prepareStatement( INSERT_CAT_LINKS );
               for ( int i = 0; i < cats.length; i++ ) {
//                        "INSERT INTO activity_cats (id_activity, id_category) " +
//                        "VALUES (?,?)";
                    ps.clearParameters();
                    ps.setLong( 1, bean.getId() );
                    ps.setLong( 2, cats[ i ].getId() );
                    ps.executeUpdate();
               }
               ps.close();
          } catch ( SQLException sqlEx ) {
               throw sqlEx;
          } finally {
               con.close();
          }
     }

     public Category[] get_cats_for_activity( long activity_id )
        throws SQLException {
          Connection con = getConnection();
          ArrayList items = new ArrayList();

          try {
               PreparedStatement ps = con.
                  prepareStatement( GET_CATS_FOR_ACTIVITY );
               ps.setLong( 1, activity_id );
               ResultSet rs = ps.executeQuery();
               int last_id = -1;
               Category item = null;
//                  "SELECT cats.id, cats.lang, cats.name " +
//                  "FROM categories cats, activity_cats ac " +
//                  "WHERE cats.id=ac.id_category " +
//                  "AND ac.id_activity=? " +
//                  "ORDER BY cats.lang ASC, cats.name ASC ";
               while ( rs.next() ) {
                    int item_id = rs.getInt( 1 );
                    if ( last_id != item_id ) {
                         item = new Category();
                         item.setId( item_id );
                         items.add( item );
                    }

                    if ( item == null ) {
                         System.out.println( "DBStuff.get_cats_for_activity" );
                         System.out.println( "never code" );
                    }
                    item.getName().setString( rs.getString( 2 ), rs.getString( 3 ) );
               }
               rs.close();
               ps.close();
          } catch ( SQLException sqlEx ) {
               throw sqlEx;
          } finally {
               con.close();
          }

          Category[] result = null;
          if ( items.size() > 0 ) {
               result = new Category[ items.size() ];
               items.toArray( result );
          }

          return result;
     }

     /**
      * @param activity_id
      *
      * @throws SQLException
      */
     public void delete_objects( long activity_id )
        throws SQLException {
          Connection con = getConnection();

          try {
               PreparedStatement ps = con.prepareStatement( DELETE_OBJS_FOR_ACTIVITY );
               ps.setLong( 1, activity_id );
               ps.executeUpdate();
               ps.close();
          } catch ( SQLException sqlEx ) {
               throw sqlEx;
          } finally {
               con.close();
          }
     }

     public static final String GET_CATS_FOR_ACTIVITY =
        "SELECT cats.id, cats.lang, cats.name " +
        "FROM categories cats, activity_cats ac " +
        "WHERE cats.id=ac.id_category " +
        "AND ac.id_activity=? " +
        "ORDER BY cats.lang ASC, cats.name ASC ";

     public static final String DELETE_CATS_FOR_ACTIVITY =
        "DELETE FROM activity_cats " +
        "WHERE id_activity=?";
     public static final String DELETE_OBJS_FOR_ACTIVITY =
        "DELETE FROM objects " +
        "WHERE activity_id=?";

     public static final String INSERT_CAT_LINKS =
        "INSERT INTO activity_cats (id_activity, id_category) " +
        "VALUES (?,?)";

     public static final String INSERT_DONOR_ID =
        "INSERT INTO donor_ids (activity_id, org_id, donor_id) " +
        "VALUES (?,?,?)";

     public static final String DELETE_DONOR_IDS_FOR_ACT =
        "DELETE FROM donor_ids WHERE activity_id=?";

     public static final String GET_DONOR_IDS_FOR_ACT =
        "SELECT org_id, donor_id FROM donor_ids WHERE activity_id=?";


}
